#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 2018/1/17 0017 13:18
# @Author  : Arliki
# @email   : hkdnxycz@outlook.com
# @File    : mysql_db

import pymysql
import logging
import logging.config
from decimal import *


class Mysql(object):
    def __init__(self, *config):
        logging.config.fileConfig(open('./logger.conf', encoding='utf-8'))
        self._logger = logging.getLogger('MysqlError')
        if len(config) != 5:
            self._logger.warning("config信息设置错误 需按顺序设置为 host,port,user,passwd,db_name")
            exit()
        __host = config[0]
        __port = config[1]
        __user = config[2]
        __passwd = config[3]
        __db = config[4]
        print(__db)
        self.__con = pymysql.connect(host=__host, port=__port, user=__user, passwd=__passwd, db=__db)

    def __del__(self):
        self.__con.close()

    def MS(self, table, columns, condition, *, orderby="''", groupby=''):
        cur1 = self.__con.cursor()
        self._check_input(table)
        columns = self._check_input(columns, 0, '*')
        condition = self._check_input(condition, 0, '1')
        if type(condition) == dict:
            condition = self.dict2str(condition, 'and')
        if len(groupby) < 1:
            sql = "select %s from %s where %s order by %s " % (columns, table, condition, orderby)
        else:
            sql = "select %s from %s where %s group by '%s' order by %s " % (
                columns, table, condition, groupby, orderby)
        columns = columns.split(',')
        if columns == '*':
            cur2 = self.__con.cursor()
            n1 = cur2.execute("show columns from %s" % table)
            columns = []
            for row in cur2.fetchall():
                columns.append(row[0])
            cur2.close()
        num = cur1.execute(sql)
        res = {}
        if num > 0:
            index = 0
            for row in cur1.fetchall():
                res[index] = {}
                for i in range(len(columns)):
                    if type(row[i]) == Decimal:
                        new_data = '%.2f' % row[i]
                        res[index][columns[i]] = new_data
                    else:
                        res[index][columns[i]] = row[i]
                index += 1
        cur1.close()
        return res

    def select(self, sql):
        cur = self.__con.cursor()
        cur.execute(sql)
        lis = list()
        for i in cur.fetchall():
            if len(i) == 1:
                lis.append(list(i))
            else:
                L = list(i)
                for j in range(len(L)):
                    if type(L[j]) == Decimal:
                        L[j] = '%.2f' % L[j]
                lis.append(L)
        cur.close()
        return lis

    def insert(self, table, data={}, columns="''", select=False):
        self._check_input(table)
        cur = self.__con.cursor()
        if select:
            sql = "insert into %s %s (%s)" % (table, tuple(columns), data)
        else:
            columns, data = self.datasplit(data)
            sql = "insert into %s %s VALUES %s " % (table, columns, data)
        try:
            cur.execute(sql)
            self.__con.commit()
        except:
            self.__con.rollback()
        finally:
            cur.close()

    def update(self, table, data, columns):
        cur = self.__con.cursor()
        self._check_input(table)
        self._check_input(columns)
        self._check_input(data)
        if type(columns) == dict:
            columns = self.dict2str(columns, 'and')
        if type(data) == dict:
            data = self.dict2str(data, 'and')
        sql = "update %s set %s where %s " % (table, columns, data)
        try:
            cur.execute(sql)
            self.__con.commit()
        except:
            self.__con.rollback()
        finally:
            cur.close()

    def delete(self, columns, data):
        cur = self.__con.cursor()
        data = self.dict2str(data, 'and')
        sql = "delete from %s where %s" % (columns, data)
        cur.execute(sql)
        cur.close()

    def dict2str(self, dic, cn):
        str1 = ""
        if type(dic) == dict:
            lis = list(dic)
            for x in range(len(lis)):
                str1 += " " + str(lis[x]) + "=" + str(dic[lis[x]]) + " " + cn
            return str1[1:-len(cn)]
        else:
            return dic

    def datasplit(self, dic):
        if type(dic) == dict:
            k = tuple(dic)
            v = list()
            for i in range(len(k)):
                v.append(str(dic[k[i]]))
            return k, tuple(v)

    def _check_input(self, checked, lenth=0, default=''):
        if default == '':
            if len(checked) == lenth:
                self._logger.warning('%s is None' % checked)
                exit()
        else:
            if len(checked) == lenth:
                return default
            else:
                return checked